const sqlite3 = require('sqlite3').verbose()
const path = require('path')

const users = new sqlite3.Database(path.resolve(__dirname, './databases/users.db'))
users.run(`DROP TABLE IF EXISTS users`, err => {
    if (err) {
        console.error(err)
        return
    }
    console.log('Users table dropped')
    users.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT NOT NULL,
    password TEXT NOT NULL,
    email TEXT NOT NULL,
    description TEXT DEFAULT '翰如海，翰如山；通如天，通如地；启如日，启如月；文如花，文如草。' NOT NULL,
    passages TEXT NOT NULL DEFAULT '[]',
    op_identity TEXT DEFAULT NULL,
    is_op INTEGER NOT NULL DEFAULT 0,
    is_banned INTEGER NOT NULL DEFAULT 0,
    is_admin INTEGER NOT NULL DEFAULT 0,
    profile_pic BLOB DEFAULT NULL,
    cover_pic BLOB DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)`, err => {
        if (err) {
            console.error(err)
            return
        }
        console.log('Users table created')
        users.run(`DROP INDEX IF EXISTS idx_users`, err => {
            if (err) {
                console.error(err)
                return
            }
            console.info('Index idx_users dropped')
            users.run(`CREATE INDEX IF NOT EXISTS idx_users ON users(id, name, email,` +
                ` is_banned, is_admin, is_op, op_identity, created_at, passages, description, profile_pic, cover_pic)`, err => {
                if (err)
                    console.error(err)
                else{
                    console.info('Index idx_users created')
                    users.run(`INSERT INTO users(name, password, email, is_admin, is_op, op_identity) 
                        VALUES('admin', 'admin', 'none', 1, 1, 'admin')`, err => {
                        if (err)
                            console.error(err)
                        else
                            console.info('Admin user created')
                    })
                }
            })
        })
    })
})
users.run(`DROP TABLE IF EXISTS code`, err => {
    if (err) {
        console.error(err)
        return
    }
    console.info('Code table dropped')
    users.run(`CREATE TABLE IF NOT EXISTS code (
    id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
    code TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)`)
})

const passages = new sqlite3.Database(path.resolve(__dirname, './databases/passages.db'))
passages.run(`DROP TABLE IF EXISTS passages`, err => {
    if (err) {
        console.error(err)
        return
    }
    console.info('Passages table dropped')
    passages.run(`CREATE TABLE IF NOT EXISTS passages (
    id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT NOT NULL,
    update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    comments TEXT NOT NULL DEFAULT '[]')`, err => {
        if (err) {
            console.error(err)
            return
        }
        console.info('Passages table created')
        passages.run(`DROP INDEX IF EXISTS idx_passages`, err => {
            if (err) {
                console.error(err)
                return
            }
            console.info('Index idx_passages dropped')
            passages.run(`CREATE INDEX IF NOT EXISTS idx_passages ON passages(id, title, author, update_at, content, comments)`, err => {
                if (err)
                    console.error(err)
                else
                    console.info('Index idx_passages created')
            })
        })
    })
})